﻿using SCRUM.Core.DataProviders;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Configuration.Provider;
using SCRUM.Core.MembershipProviders;

namespace SCRUM.Core.MembershipProviders
{
    /// <summary>
    /// Privider for comunication with the database
    /// </summary>
    public partial class MembershipDBDataProvider : MembershipDataProvider
    {
        /// <summary>
        /// Connection string name
        /// </summary>
        private string connStringName;

        /// <summary>
        /// Connection string from the web.config
        /// </summary>
        private string connString;

        /// <summary>
        /// Connection to the database
        /// </summary>
        public static DbConnection connection;

        /// <summary>
        /// Provider factory
        /// </summary>
        private static DbProviderFactory database;

        /// <summary>
        /// Parameter prefix
        /// </summary>
        private static string parmPrefix;


        /// <summary>
        /// Initializes the provider.
        /// </summary>
        /// <param name="name">The friendly name of the provider.</param>
        /// <param name="config">A collection of the name/value pairs representing the provider-specific attributes specified in the configuration for this provider.</param>
        /// <exception cref="T:System.ArgumentNullException">
        /// The name of the provider is null.
        /// </exception>
        /// <exception cref="T:System.ArgumentException">
        /// The name of the provider has a length of zero.
        /// </exception>
        /// <exception cref="T:System.InvalidOperationException">
        /// An attempt is made to call <see cref="M:System.Configuration.Provider.ProviderBase.Initialize(System.String,System.Collections.Specialized.NameValueCollection)"/> on a provider after the provider has already been initialized.
        /// </exception>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (String.IsNullOrEmpty(name))
            {
                name = "MembershipDBDataProvider";
            }

            if (String.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Generic Database Provider");
            }

            base.Initialize(name, config);

            if (config["connectionStringName"] == null)
            {
                // default to DataEngine
                config["connectionStringName"] = "DataSource";
            }

            connStringName = config["connectionStringName"];
            config.Remove("connectionStringName");

            // loading connection string
            connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;

            if (config["parmPrefix"] == null)
            {
                // default
                config["parmPrefix"] = "@";
            }

            parmPrefix = config["parmPrefix"];
            config.Remove("parmPrefix");

            // loading database
            string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;
            database = DbProviderFactories.GetFactory(providerName);

            // creating connection when knowing the database
            if (connection == null)
            {
                connection = database.CreateConnection();
                connection.ConnectionString = connString;
            }
            
            // Throw an exception if unrecognized attributes remain
            if (config.Count > 0)
            {
                string attr = config.GetKey(0);
                if (!String.IsNullOrEmpty(attr))
                {
                    throw new ProviderException("Unrecognized attribute: " + attr);
                }
            }
        }

        /// <summary>
        /// Executes simple query (INSERT/UPDATE/DELETE)
        /// </summary>
        /// <param name="queryID">ID of queryID from DBDataProvider.xml in XPATH format</param>
        /// <param name="parameters">parameters for this queryID</param>
        /// <returns>number of rows affected</returns>
        public static int ExecuteNonQuery(string queryID, params DbParameter[] parameters)
        {
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = CommandResources.GetCommand(queryID);

                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }

                return command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Executes "NonQuery" in transaction.
        /// </summary>
        /// <param name="queryID">The XPath query.</param>
        /// <param name="parameters">The parameters.</param>
        protected void ExecuteNonQueryTransaction(string queryID, params DbParameter[] parameters)
        {
            DbTransaction transaction = connection.BeginTransaction();
            try
            {
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = CommandResources.GetCommand(queryID);
                    foreach (DbParameter parameter in parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                }
            }
            catch
            {
                transaction.Rollback();
            }
            finally
            {
                transaction.Dispose();
            }
        }

        /// <summary>
        /// Returns one single value from queryID and it's parameters
        /// </summary>
        /// <param name="queryID">ID of queryID from DBDataProvider.xml in XPATH format</param>
        /// <param name="parameters">parameters for this queryID</param>
        /// <returns>1x1 set od data (only one single value)</returns>
        public static object GetScalar(string queryID, params DbParameter[] parameters)
        {
            DbDataReader data = GetVector(queryID, parameters);

            if (data.FieldCount > 1)
            {
                throw new Exception(String.Format("Database returned more than one (expected) field for query {0}", queryID));
            }

            data.Read();
            return data.GetValue(0);
        }

        /// <summary>
        /// Creates DBParameter from name and value, which is used in DBCommand
        /// </summary>
        /// <param name="name">Name of parameter (without paramPrefix like "@")</param>
        /// <param name="value">Value of parameter</param>
        /// <returns>DbParameter with specified name and value</returns>
        public static DbParameter CreateParameter(string name, object value)
        {
            DbParameter parameter = database.CreateParameter();
            parameter.ParameterName = parmPrefix + name;
            parameter.Value = value;

            return parameter;
        }

        /// <summary>
        /// Returns 1D set of data from queryID and it's parameters
        /// </summary>
        /// <param name="queryID">ID of queryID from DBDataProvider.xml in XPATH format</param>
        /// <param name="parameters">parameters for this queryID</param>
        /// <returns>1D set of data from queryID</returns>
        public static DbDataReader GetVector(string queryID, params DbParameter[] parameters)
        {
            DbDataReader data = GetGrid(queryID, parameters);

            if (data.RecordsAffected > 1)
            {
                throw new Exception(String.Format("Database returned more than one (expected) row for query {0}", queryID));
            }

            return data;
        }

        /// <summary>
        /// Returns 2D set of data from queryID and it's parameters
        /// </summary>
        /// <param name="queryID">ID of queryID from DBDataProvider.xml in XPATH format</param>
        /// <param name="parameters">parameters for this queryID</param>
        /// <returns>2D set of data from queryID</returns>
        public static DbDataReader GetGrid(string queryID, params DbParameter[] parameters)
        {
            string command = CommandResources.GetCommand(queryID);
            return GetGridByQuery(command, parameters);
        }

        /// <summary>
        /// Returns 2D set of data from query and it's parameters
        /// </summary>
        /// <param name="query">query in raw text</param>
        /// <param name="parameters">parameters for this queryID</param>
        /// <returns>2D set of data from queryID</returns>
        public static DbDataReader GetGridByQuery(string query, params DbParameter[] parameters)
        {
            using (DbCommand command = connection.CreateCommand())
            {
                command.CommandText = query;

                foreach (DbParameter parameter in parameters)
                {
                    command.Parameters.Add(parameter);
                }

                return command.ExecuteReader();
            }
        }

        /// <summary>
        /// Returns int value of column specified by the name
        /// </summary>
        /// <param name="data">The specified data.</param>
        /// <param name="column">The column.</param>
        /// <returns>Int value of column specified by the name</returns>
        public static string GetStringByColumn(DbDataReader data, string column)
        {
            return data.GetString(data.GetOrdinal(column));
        }

        /// <summary>
        /// Returns string value of column specified by the name
        /// </summary>
        /// <param name="data">The specified data.</param>
        /// <param name="column">The column.</param>
        /// <returns>String value of column specified by the name</returns>
        public static int GetIntByColumn(DbDataReader data, string column)
        {
            return data.GetInt32(data.GetOrdinal(column));
        }

        /// <summary>
        /// Returns Boolean value of column specified by the name
        /// </summary>
        /// <param name="data">The specified data.</param>
        /// <param name="column">The column.</param>
        /// <returns>String value of column specified by the name</returns>
        public static bool GetBoolByColumn(DbDataReader data, string column)
        {
            return data.GetBoolean(data.GetOrdinal(column));
        }
    }
}
